﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using bdnt_pets_system_Entity;
using System.Data;

namespace bdnt_pets_system_Bo.SystemManager
{
    public class MatePartManager
    {
        AccessHelper accessHelper = AccessHelper.GetInstance();

        public bool SaveObj(Tbl_MatePart cuObj)
        {
            StringBuilder sql = new StringBuilder();
            //新增
            if (cuObj.Id == 0)
            {
                sql.AppendFormat(@"INSERT INTO Tbl_MatePart
                               (VINNO,PARTNO,BARNO,CREATETIME,CREATEUSERID)
                                VALUES('{0}','{1}','{2}','{3}',{4})
                                ", cuObj.VINNO, cuObj.PARTNO, cuObj.BARNO,
                (cuObj.CREATETIME.HasValue && cuObj.CREATETIME.Value.Year != 1900 ?
                "to_date('" + cuObj.CREATETIME.Value.ToString("yyyy-MM-dd") + "','yyyy-MM-dd')" : "NULL"),
                  cuObj.CREATEUSERID);


                var cure = accessHelper.ExecuteScalar(sql.ToString());
                if (cure == null)
                    return false;
                return true;
            }
            else
            {
                sql = sql.AppendFormat(@"UPDATE Tbl_MatePart
               SET VINNO = '{0}'
                ,PARTNO = '{1}' 
                ,BARNO = '{2}' 
                 WHERE Id={3}", cuObj.VINNO, cuObj.PARTNO, cuObj.BARNO,
                cuObj.Id);

                var re = accessHelper.ExcultData(sql.ToString());
                return re;
            }
        }

        public List<Tbl_MatePart> GetObjListByCondition(string vinNo, int pageSize, int currentPage)
        {
            StringBuilder sql = new StringBuilder(@"select row_number() over(order by mt.Id desc) recordId,mt.*
            ,pt.PartName,pt.en_PartName,(select empname from tbl_employee where id=mt.createuserid and rownum=1) empname
            from tbl_matepart mt 
            left join Tbl_Part pt on mt.PartNo=pt.PartNo where 1=1");

            if (!string.IsNullOrEmpty(vinNo))
                sql = sql.AppendFormat(@" and mt.vinno like '%{0}%'", vinNo);

            int startNum = pageSize * (currentPage > 0 ? currentPage - 1 : 0);
            int endNum = pageSize * currentPage;
            sql.Insert(0, "select * from (");
            sql.AppendFormat(@") re where re.recordId>{0} and re.recordId<={1}", startNum, endNum);
            var dt = accessHelper.ExcuteDataTable(sql.ToString());

            List<Tbl_MatePart> returnlist = new List<Tbl_MatePart>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_MatePart
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    BARNO = dr["BARNO"] == DBNull.Value ? "" : dr["BARNO"].ToString(),
                    CREATEUSERID = dr["CREATEUSERID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["CREATEUSERID"]),
                    PARTNO = dr["PARTNO"] == DBNull.Value ? "" : dr["PARTNO"].ToString(),
                    VINNO = dr["VINNO"] == DBNull.Value ? "" : dr["VINNO"].ToString(),
                    CREATETIME = dr["CREATETIME"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["CREATETIME"].ToString()),
                    CreateUserName = dr["empname"] == DBNull.Value ? "" : dr["empname"].ToString(),
                    en_PartName = dr["en_PartName"] == DBNull.Value ? "" : dr["en_PartName"].ToString(),
                    PartName = dr["PartName"] == DBNull.Value ? "" : dr["PartName"].ToString()
                });
            }
            return returnlist;
        }

        public int GetObjListByConditionCount(string vinNo)
        {
            StringBuilder sql = new StringBuilder(@"select count(mt.Id) allCount
            from tbl_matepart mt 
            left join Tbl_Part pt on mt.PartNo=pt.PartNo where 1=1");

            if (!string.IsNullOrEmpty(vinNo))
                sql = sql.AppendFormat(@" and mt.vinno like '%{0}%'", vinNo);
            
            var dt = accessHelper.ExecuteScalar(sql.ToString());
            if (dt != null)
                return Int32.Parse(dt.ToString());
            return 0;
        }
    }
}
